/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Project2.cs342.project1.mathNotebook.Database;

import java.sql.*;
import java.util.*;

/**
 *
 * @author Rick
 */
public class DBGroupAccess {

    static String id = null;
    static String file_name = null;
    static Connection conn = null;
    PreparedStatement ps = null;
    SQLConfigeration sqlConfig;

    public DBGroupAccess(){
        sqlConfig = new SQLConfigeration();
        conn = sqlConfig.getConnection();
    }

    /**
     * inserts a given fiel into a given group
     *
     * @param groupName the group to be inserted into
     * @param filename the file that is to be inserted
     */
    public void insertIntoGroupAccess(String groupName, String filename){

        //Insert data
        String groupAccess = "insert into GroupAccess(groupname,filename)" +
                                                    "values (?,?)";
        
        
        try{
            conn.setAutoCommit(false);
            
            ps = conn.prepareStatement(groupAccess);
            //ps.setInt(1, 0);
            ps.setString(1, groupName);
            ps.setString(2, filename);
            System.out.println("Attempting insert");
            ps.executeUpdate();
            conn.commit();
            System.out.println("Insert successful");

        }catch(Exception ex){
            System.out.println("Insert Error " + ex + " at:");
            ex.printStackTrace();
        }finally{
            try {
                ps.close();
                
            } catch (SQLException ex) {
                System.out.println("Sql Error " + ex);
            }
        }

    }

    /**
     * Retrieves the groups that are associated with a given file
     *
     * @param filename the fle that the groups are associated with
     * @return an array of groups that are associated with the given file
     */
    public String[] retrieveGroupForFiles(String filename){
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        String query = "Select groupname from GroupAccess where groupname IS NOT NULL AND filename = ?";
        String[] groupname = new String[50];

        try{
            pstmt = conn.prepareStatement(query);

            pstmt.setString(1, filename);
            rs = pstmt.executeQuery();

            //get the binary blob data onto the client
            System.out.println("Attempting to get the Files");
            int index = 0;
            while(rs.next()){
                groupname[index] = rs.getString("groupname");
                //System.out.println("Recieved: " + filename[index]);
                index++;
            }

        }catch(Exception ex){System.out.println("Retrieval Error: " + ex);}
        finally{
            try {
                rs.close();
                pstmt.close();
                //conn.close();
            } catch (SQLException ex) {
                System.out.println("SQL Error: " + ex);
            }
        }
        return groupname;
    }

    /**
     * Retrieves the files associated with a given group
     *
     * @param groupName the name of the group that the files are associated with
     * @return an array of files associated with the given group
     */
    public String[] retrieveFilesForGroup(String groupName){
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        String query = "Select filename from GroupAccess where filename IS NOT NULL AND groupname = ?";
        String[] filename = new String[50];

        try{
            pstmt = conn.prepareStatement(query);

            pstmt.setString(1, groupName);
            rs = pstmt.executeQuery();

            //get the binary blob data onto the client
            System.out.println("Attempting to get the Files");
            int index = 0;
            while(rs.next()){
                filename[index] = rs.getString("filename");
                //System.out.println("Recieved: " + filename[index]);
                index++;
            }
            
        }catch(Exception ex){System.out.println("Retrieval Error: " + ex);}
        finally{
            try {
                rs.close();
                pstmt.close();
                //conn.close();
            } catch (SQLException ex) {
                System.out.println("SQL Error: " + ex);
            }

        }
         ArrayList<String> list = new ArrayList<String>();
        for (int i = 0; i < filename.length;i++){
        if (filename[i]!=null){
            
                list.add(filename[i]);
                System.out.println(filename[i]);
            }
        }
        String[] filename2 = new String[list.size()];
        list.toArray(filename2);
        return filename2;


    }

    /**
     * Deletes a file from a given group
     * @param groupname the group the files are associated with
     * @param filename the filename to be deleted
     */
    public void deleteRowFromGroupAccess(String groupname, String filename){
        String deleteNotebook = "delete from GroupAccess where groupname = '" +
                groupname + "' and filename = '" + filename + "'";
        try{
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(deleteNotebook);
            System.out.println("Attempting Deletion");
            ps.executeUpdate();
            conn.commit();
            System.out.println("Delection Successful");

        }catch(SQLException ex){
            System.out.println("Deletion Error: " + ex);
        }

    }
}
